﻿/*
 * Programer: Jeffrey Padfield
 * Description: MySQL database handler
 * Version: 2
 * Date: 01/27/2014
 * Based off the work of: Etienne Rached
 * Work found at: http://www.codeproject.com/Articles/43438/Connect-C-to-MySQL
 */

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using MySql.Data.MySqlClient;

namespace CWSaver.MySQL
{
    public class MySqlDatabase
    {
        private MySqlConnection connection;

        /// <summary>
        /// Default Constructor.
        /// </summary>
        public MySqlDatabase()
        {
            
        }

        /// <summary>
        /// Constructor that builds the connection string. And saves them to the programs settings.
        /// </summary>
        /// <param name="server">MySQL Server.</param>
        /// <param name="database">MySQL database name.</param>
        /// <param name="user">MySQL user name.</param>
        /// <param name="password">MySQL password.</param>
        public MySqlDatabase(string server, string database, string user, string password)
        {
            string connectionString = "SERVER=" + server + ";" + "DATABASE" + database + "UID=" + user + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);

            Properties.Settings.Default.mysqlPassword = password;
            Properties.Settings.Default.mysqlServer = server;
            Properties.Settings.Default.mysqlUser = user;
            Properties.Settings.Default.Save();
        }

        /// <summary>
        /// Opens the connection to MySql.
        /// </summary>
        /// <returns>True if connection opens. False if connection failed to open.</returns>
        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException exception)
            {
                return false;
            }
        }

        /// <summary>
        /// Closes the connection to MySql.
        /// </summary>
        /// <returns>True if the connection closes. False if connection failed to close.</returns>
        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// Creates a new database.
        /// </summary>
        /// <param name="sqlData">SQL query to execute.</param>
        public void CreateDatabase(string sqlData)
        {
            if (OpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sqlData, connection);

                cmd.ExecuteNonQuery();

                CloseConnection();
            }
        }

        /// <summary>
        /// Creates a new table within the database.
        /// </summary>
        /// <param name="sqlData">SQL query to execute.</param>
        public void CreateTable(string sqlData)
        {
            if (OpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sqlData, connection);

                cmd.ExecuteNonQuery();

                CloseConnection();
            }
        }

        /// <summary>
        /// Inserts data into the database.
        /// </summary>
        /// <param name="sqlData">SQL query to execute.</param>
        public void InsertData(string sqlData)
        {
            if (OpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sqlData, connection);

                cmd.ExecuteNonQuery();

                CloseConnection();
            }
        }

        /// <summary>
        /// Updates data in the databasse.
        /// </summary>
        /// <param name="sqlData">SQL query to execute.</param>
        public void UpdateData(string sqlData)
        {
            if (OpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sqlData, connection);

                cmd.ExecuteNonQuery();
                
                CloseConnection();
            }
        }

        /// <summary>
        /// Deletes record from the database.
        /// </summary>
        /// <param name="sqlData">SQL query to execute.</param>
        public void DeleteData(string sqlData)
        {
            if (OpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sqlData, connection);

                cmd.ExecuteNonQuery();

                CloseConnection();
            }
        }

        /// <summary>
        /// Gets the data from the database.
        /// </summary>
        /// <param name="sqlData">SQL query to execute.</param>
        /// <returns>A list of all objects in the table.</returns>
        public List<string>[] GetData(string sqlData, string[] columnStrings)
        {
            List<string>[] dataList = new List<string>[columnStrings.Count()];
            if (OpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sqlData, connection);

                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    for (int i = 0; i < columnStrings.Count(); i++)
                    {
                        dataList[i].Add(dataReader[columnStrings[i]] + "");
                    }
                }

                dataReader.Close();

                CloseConnection();

                return dataList;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Counts all items within a table.
        /// </summary>
        /// <param name="sqlData">SQL query to execute.</param>
        /// <returns>How many items are in the table.</returns>
        public int CountData(string sqlData)
        {
            int count = -1;
            if (OpenConnection())
            {
                MySqlCommand cmd = new MySqlCommand(sqlData, connection);

                count = int.Parse(cmd.ExecuteScalar() + "");

                CloseConnection();

                return count;
            }
            else
            {
                return count;
            }
        }

        /// <summary>
        /// Creates a backup of the database.
        /// </summary>
        /// <param name="database"></param>
        public void Backup(string database)
        {
            try
            {
                DateTime time = DateTime.Now;
                int year = time.Year;
                int month = time.Month;
                int day = time.Day;

                string path = @"\" + database + year + "-" + month + "-" + day + ".sql";

                StreamWriter file = new StreamWriter(path);

                ProcessStartInfo psi = new ProcessStartInfo();
                psi.FileName = "mysqldump";
                psi.RedirectStandardInput = false;
                psi.RedirectStandardOutput = true;
                psi.Arguments = string.Format(@"-u{0} -p{1} - h{2} {3}", Properties.Settings.Default.mysqlUser,
                    Properties.Settings.Default.mysqlPassword, Properties.Settings.Default.mysqlServer, database);
                psi.UseShellExecute = false;

                Process process = Process.Start(psi);

                string output = process.StandardOutput.ReadToEnd();
                file.WriteLine(output);
                process.WaitForExit();
                file.Close();
                process.Close();
            }
            catch (IOException exception)
            {
                IOException ioException = new IOException("Error with creating file.", exception);
                throw ioException;
            }
            catch (NullReferenceException exception)
            {
                NullReferenceException nullReference = new NullReferenceException("Output is null.", exception);
                throw nullReference;
            }
        }

        /// <summary>
        /// Restores a database.
        /// </summary>
        /// <param name="backup"></param>
        /// <param name="database"></param>
        public void Restore(string backup, string database)
        {
            try
            {
                StreamReader file = new StreamReader(backup);
                string input = file.ReadToEnd();
                file.Close();

                ProcessStartInfo psi = new ProcessStartInfo();
                psi.FileName = "mysql";
                psi.RedirectStandardInput = true;
                psi.RedirectStandardOutput = false;
                psi.Arguments = string.Format(@"-u{0} -p{1} - h{2} {3}", Properties.Settings.Default.mysqlUser,
                    Properties.Settings.Default.mysqlPassword, Properties.Settings.Default.mysqlServer, database);
                psi.UseShellExecute = false;

                Process process = Process.Start(psi);
                process.StandardInput.WriteLine(input);
                process.StandardInput.Close();
                process.WaitForExit();
                process.Close();

            }
            catch (IOException exception)
            {
                IOException ioException = new IOException("Error with reading file.", exception);
                throw ioException;
            }
            catch (NullReferenceException exception)
            {
                NullReferenceException nullReference = new NullReferenceException("Input is null.", exception);
                throw nullReference;
            }
        }
    }
}
